<!DOCTYPE html>
<!--[if IE 8]><html class="no-js lt-ie9" lang="en" > <![endif]-->
<!--[if gt IE 8]><!-->
<html class="no-js" lang="en">
<!--<![endif]-->
<head>
    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">

    <title>Operations on a graph of multiple entity types - The .NET Core ORM Cookbook</title>
    <link rel="shortcut icon" href="favicon.ico">
    <link rel="stylesheet" href="css/theme.css" type="text/css" />
    <link rel="stylesheet" href="css/theme_colors.css" type="text/css" />
    <link rel="stylesheet" href="css/styles/vs.css">
    <link rel="stylesheet" href="css/font-awesome.4.5.0.min.css">
</head>
<body role="document">
    <div class="grid-for-nav">
        <nav data-toggle="nav-shift" class="nav-side stickynav">
            <div class="side-nav-search">
                <a href="index.htm"><i class="fa fa-home"></i> The .NET Core ORM Cookbook</a>
                <div role="search">
                    <form id="search-form" class="form" action="Docnet_search.htm" method="get">
                        <input type="text" name="q" placeholder="Search docs" />
                    </form>
                </div>
            </div>
            <div class="menu menu-vertical" data-spy="affix" role="navigation" aria-label="main navigation">
<ul>
<li class="tocentry"><a href="index.htm">Home</a>
</li>

<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="ORMs.htm">ORMs</a></span>
</li>
<li class="tocentry"><a href="FAQ.htm">FAQ</a>
</li>
<li class="tocentry">
<ul>
<li><span class="navigationgroup"><i class="fa fa-caret-down"></i> <a href="StandardCRUDscenarios.htm">Standard CRUD scenarios</a></span></li>
<li class="tocentry"><a href="SingleModelCrud.htm">Operations on a single entity type</a>
</li>
<li class="tocentry"><a href="MultipleCrud.htm">Operations on sets</a>
</li>
<li class="tocentry current"><a class="current" href="ModelWithChildren.htm">Operations on a graph of multiple entity types</a>
<ul class="currentrelative">
<li class="tocentry"><a href="#scenario-prototype">Scenario Prototype</a></li>

<li class="tocentry"><a href="#ado.net">ADO.NET</a></li>

<li class="tocentry"><a href="#chain">Chain</a></li>

<li class="tocentry"><a href="#dapper">Dapper</a></li>

<li class="tocentry"><a href="#dbconnector">DbConnector</a></li>

<li class="tocentry"><a href="#entity-framework-6">Entity Framework 6</a></li>

<li class="tocentry"><a href="#entity-framework-core">Entity Framework Core</a></li>

<li class="tocentry"><a href="#linq-to-db">LINQ to DB</a></li>

<li class="tocentry"><a href="#llblgen-pro">LLBLGen Pro</a></li>

<li class="tocentry"><a href="#nhibernate">NHibernate</a></li>

<li class="tocentry"><a href="#repodb">RepoDb</a></li>

<li class="tocentry"><a href="#servicestack">ServiceStack</a></li>



</ul>
<li class="tocentry"><a href="Immutable.htm">Operations on immutable entities</a>
</li>
<li class="tocentry"><a href="TryCrud.htm">Handling failures and exceptions</a>
</li>
<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="OperationswithaForeignKeybasedLookupTable.htm">Operations with a Foreign Key based Lookup Table</a></span>
</li>

</ul>
</li>
<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="Fetchingdatascenarios.htm">Fetching data scenarios</a></span>
</li>
<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="Advancedscenarios.htm">Advanced scenarios</a></span>
</li>
<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="Sortingscenarios.htm">Sorting scenarios</a></span>
</li>
<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="Storedprocedurescenarios.htm">Stored procedure scenarios</a></span>
</li>
<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="Auditingandhistoryscenarios.htm">Auditing and history scenarios</a></span>
</li>
<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="Multi-Tenancyscenarios.htm">Multi-Tenancy scenarios</a></span>
</li>
<li class="tocentry">
<span class="navigationgroup"><i class="fa fa-caret-right"></i> <a href="UnknownDatabasescenarios.htm">Unknown Database scenarios</a></span>
</li>
</ul>
				<div class="toc-footer">
					<span class="text-small">
						<hr/>
						<a href="https://github.com/FransBouma/DocNet" target="_blank">Made with <i class="fa fa-github"></i> DocNet</a>
					</span>
				</div>	
			</div>
            &nbsp;
        </nav>
        <section data-toggle="nav-shift" class="nav-content-wrap">
            <nav class="nav-top" role="navigation" aria-label="top navigation">
                <i data-toggle="nav-top" class="fa fa-bars"></i>
                <a href="index.htm">The .NET Core ORM Cookbook</a>
            </nav>
            <div class="nav-content">
                <div role="navigation" aria-label="breadcrumbs navigation">
                    <div class="breadcrumbs">
<ul><li><a href="index.htm">Home</a></li> / <li><a href="StandardCRUDscenarios.htm">Standard CRUD scenarios</a></li> / <li><a href="ModelWithChildren.htm">Operations on a graph of multiple entity types</a></li></ul>
					
                    </div>
                    <hr />
                </div>
                <div role="main">
                    <div class="section">
<h1 id="crud-operations-on-model-with-child-records">CRUD Operations on Model with Child Records<a class="headerlink" href="#crud-operations-on-model-with-child-records" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h1>
<p>This scenario demonstrates performing Create, Read, Update, and Delete operations on an object that includes a collection of child records.</p>
<h2 id="scenario-prototype">Scenario Prototype<a class="headerlink" href="#scenario-prototype" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<pre><code class="cs">public interface IProductLine&lt;TProduct&gt;
    where TProduct : IProduct
{
    int ProductLineKey { get; set; }

    string? ProductLineName { get; set; }

    ICollection&lt;TProduct&gt; Products { get; }
}
</code></pre>

<pre><code class="cs">public interface IModelWithChildrenScenario&lt;TProductLine, TProduct&gt;
   where TProductLine : class, IProductLine&lt;TProduct&gt;, new()
   where TProduct : class, IProduct, new()
{
    /// &lt;summary&gt;
    /// Create a new ProductLine row, returning the new primary key.
    /// &lt;/summary&gt;
    /// &lt;remarks&gt;This MUST save any attached Product records.&lt;/remarks&gt;
    int Create(TProductLine productLine);

    /// &lt;summary&gt;
    /// Delete a ProductLine row using an object.
    /// &lt;/summary&gt;
    /// &lt;remarks&gt;Behavior when row doesn't exist is not defined. This MUST delete any orphaned Product records.&lt;/remarks&gt;
    void Delete(TProductLine productLine);

    /// &lt;summary&gt;
    /// Delete a ProductLine row using a key.
    /// &lt;/summary&gt;
    /// &lt;remarks&gt;Behavior when row doesn't exist is not defined. This MUST delete any orphaned Product records.&lt;/remarks&gt;
    void DeleteByKey(int productLineKey);

    /// &lt;summary&gt;
    /// Get a list of product lines by name.
    /// &lt;/summary&gt;
    /// &lt;param name=&quot;productLineName&quot;&gt;Name of the product line. This is not unique.&lt;/param&gt;
    /// &lt;param name=&quot;includeProducts&quot;&gt;if set to &lt;c&gt;true&lt;/c&gt; include Product records.&lt;/param&gt;
    IList&lt;TProductLine&gt; FindByName(string productLineName, bool includeProducts);

    /// &lt;summary&gt;
    /// Gets all product lines.
    /// &lt;/summary&gt;
    IList&lt;TProductLine&gt; GetAll(bool includeProducts);

    /// &lt;summary&gt;
    /// Gets an TProductLine row by its primary key.
    /// &lt;/summary&gt;
    /// &lt;param name=&quot;employeeKey&quot;&gt;The employee key.&lt;/param&gt;
    /// &lt;param name=&quot;includeChildern&quot;&gt;if set to &lt;c&gt;true&lt;/c&gt; include Product records.&lt;/param&gt;
    TProductLine? GetByKey(int productLineKey, bool includeProducts);

    /// &lt;summary&gt;
    /// Update a ProductLine row only.
    /// &lt;/summary&gt;
    /// &lt;remarks&gt;Behavior when row doesn't exist is not defined. This MUST not save any attached Product records.&lt;/remarks&gt;
    void Update(TProductLine productLine);

    /// &lt;summary&gt;
    /// Update a ProductLine row and all of its children rows. If any product rows were removed from the Products collection, they should be ignored.
    /// &lt;/summary&gt;
    /// &lt;remarks&gt;Behavior when row doesn't exist is not defined. This MUST save any attached Product records. It MUST NOT delete any Product records that were removed from the collection.&lt;/remarks&gt;
    void UpdateGraph(TProductLine productLine);

    /// &lt;summary&gt;
    /// Update a ProductLine row and all of its children rows. If any product rows were removed from the Products collection, they should be deleted.
    /// &lt;/summary&gt;
    /// &lt;remarks&gt;Behavior when row doesn't exist is not defined. This MUST save any attached Product records. It MUST delete any Product records that were removed from the collection.&lt;/remarks&gt;
    void UpdateGraphWithChildDeletes(TProductLine productLine);

    /// &lt;summary&gt;
    /// Update a ProductLine row and all of its children rows. If any product rows were removed from the Products collection, they should be ignored. Delete any product rows in the productKeysToRemove list.
    /// &lt;/summary&gt;
    /// &lt;remarks&gt;Behavior when row doesn't exist is not defined. Behavior when a row in productKeysToRemove wasn't part of the original ProductLine is not defined.&lt;/remarks&gt;
    void UpdateGraphWithDeletes(TProductLine productLine, IList&lt;int&gt; productKeysToRemove);

    /// &lt;summary&gt;
    /// Update a Product row.
    /// &lt;/summary&gt;
    /// &lt;param name=&quot;product&quot;&gt;The product.&lt;/param&gt;
    /// &lt;remarks&gt;Behavior when row doesn't exist is not defined.&lt;/remarks&gt;
    void Update(TProduct product);
}
</code></pre>

<h2 id="ado.net">ADO.NET<a class="headerlink" href="#ado.net" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<pre><code class="cs">    public class ModelWithChildrenScenario : SqlServerScenarioBase, IModelWithChildrenScenario&lt;ProductLine, Product&gt;
    {
        public ModelWithChildrenScenario(string connectionString) : base(connectionString)
        { }

        public int Create(ProductLine productLine)
        {
            if (productLine == null)
                throw new ArgumentNullException(nameof(productLine), $&quot;{nameof(productLine)} is null.&quot;);

            const string sql = &quot;INSERT INTO Production.ProductLine ( ProductLineName ) OUTPUT Inserted.ProductLineKey VALUES (@ProductLineName);&quot;;

            using (var con = OpenConnection())
            using (var trans = con.BeginTransaction())
            {
                using (var cmd = new SqlCommand(sql, con, trans))
                {
                    cmd.Parameters.AddWithValue(&quot;@ProductLineName&quot;, productLine.ProductLineName);
                    productLine.ProductLineKey = (int)cmd.ExecuteScalar();
                    productLine.ApplyKeys();
                }

                foreach (var item in productLine.Products)
                    InsertProduct(con, trans, item);

                trans.Commit();
            }
            return productLine.ProductLineKey;
        }

        public void Delete(ProductLine productLine)
        {
            if (productLine == null)
                throw new ArgumentNullException(nameof(productLine), $&quot;{nameof(productLine)} is null.&quot;);

            const string sql = @&quot;DELETE FROM Production.Product WHERE ProductLineKey = @ProductLineKey;
DELETE FROM Production.ProductLine WHERE ProductLineKey = @ProductLineKey&quot;;

            using (var con = OpenConnection())
            using (var cmd = new SqlCommand(sql, con))
            {
                cmd.Parameters.AddWithValue(&quot;@ProductLineKey&quot;, productLine.ProductLineKey);
                cmd.ExecuteNonQuery();
            }
        }

        public void DeleteByKey(int productLineKey)
        {
            const string sql = @&quot;DELETE FROM Production.Product WHERE ProductLineKey = @ProductLineKey;
DELETE FROM Production.ProductLine WHERE ProductLineKey = @ProductLineKey&quot;;

            using (var con = OpenConnection())
            using (var cmd = new SqlCommand(sql, con))
            {
                cmd.Parameters.AddWithValue(&quot;@ProductLineKey&quot;, productLineKey);
                cmd.ExecuteNonQuery();
            }
        }

        public IList&lt;ProductLine&gt; FindByName(string productLineName, bool includeProducts)
        {
            const string sqlA = @&quot;SELECT pl.ProductLineKey, pl.ProductLineName FROM Production.ProductLine pl WHERE pl.ProductLineName = @ProductLineName;
SELECT p.ProductKey, p.ProductName, p.ProductLineKey, p.ShippingWeight, p.ProductWeight FROM Production.Product p INNER JOIN Production.ProductLine pl ON p.ProductLineKey = pl.ProductLineKey WHERE pl.ProductLineName = @ProductLineName;&quot;;

            const string sqlB = @&quot;SELECT pl.ProductLineKey, pl.ProductLineName FROM Production.ProductLine pl WHERE pl.ProductLineName = @ProductLineName;&quot;;

            var sql = includeProducts ? sqlA : sqlB;
            var results = new List&lt;ProductLine&gt;();

            using (var con = OpenConnection())
            using (var cmd = new SqlCommand(sql, con))
            {
                cmd.Parameters.AddWithValue(&quot;@ProductLineName&quot;, productLineName);
                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        results.Add(new ProductLine(reader));
                    }

                    if (includeProducts)
                    {
                        var lookup = results.ToDictionary(x =&gt; x.ProductLineKey);
                        reader.NextResult();
                        while (reader.Read())
                        {
                            var product = new Product(reader);
                            lookup[product.ProductLineKey].Products.Add(product);
                        }
                    }
                }
            }

            return results;
        }

        public IList&lt;ProductLine&gt; GetAll(bool includeProducts)
        {
            const string sqlA = @&quot;SELECT pl.ProductLineKey, pl.ProductLineName FROM Production.ProductLine pl;
SELECT p.ProductKey, p.ProductName, p.ProductLineKey, p.ShippingWeight, p.ProductWeight FROM Production.Product p INNER JOIN Production.ProductLine pl ON p.ProductLineKey = pl.ProductLineKey;&quot;;

            const string sqlB = @&quot;SELECT pl.ProductLineKey, pl.ProductLineName FROM Production.ProductLine pl;&quot;;

            var sql = includeProducts ? sqlA : sqlB;
            var results = new List&lt;ProductLine&gt;();

            using (var con = OpenConnection())
            using (var cmd = new SqlCommand(sql, con))
            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                    results.Add(new ProductLine(reader));

                if (includeProducts)
                {
                    var lookup = results.ToDictionary(x =&gt; x.ProductLineKey);
                    reader.NextResult();
                    while (reader.Read())
                    {
                        var product = new Product(reader);
                        lookup[product.ProductLineKey].Products.Add(product);
                    }
                }
            }

            return results;
        }

        public ProductLine? GetByKey(int productLineKey, bool includeProducts)
        {
            const string sqlA = @&quot;SELECT pl.ProductLineKey, pl.ProductLineName FROM Production.ProductLine pl WHERE pl.ProductLineKey = @ProductLineKey;
SELECT p.ProductKey, p.ProductName, p.ProductLineKey, p.ShippingWeight, p.ProductWeight FROM Production.Product p WHERE p.ProductLineKey = @ProductLineKey;&quot;;

            const string sqlB = @&quot;SELECT pl.ProductLineKey, pl.ProductLineName FROM Production.ProductLine pl WHERE pl.ProductLineKey = @ProductLineKey;&quot;;

            var sql = includeProducts ? sqlA : sqlB;
            ProductLine result;

            using (var con = OpenConnection())
            using (var cmd = new SqlCommand(sql, con))
            {
                cmd.Parameters.AddWithValue(&quot;@ProductLineKey&quot;, productLineKey);
                using (var reader = cmd.ExecuteReader())
                {
                    if (reader.Read())
                        result = new ProductLine(reader);
                    else
                        return null;

                    if (includeProducts)
                    {
                        reader.NextResult();
                        while (reader.Read())
                            result.Products.Add(new Product(reader));
                    }
                }
            }

            return result;
        }

        public void Update(ProductLine productLine)
        {
            if (productLine == null)
                throw new ArgumentNullException(nameof(productLine), $&quot;{nameof(productLine)} is null.&quot;);

            using (var con = OpenConnection())
                UpdateProductLine(con, null, productLine);
        }

        public void Update(Product product)
        {
            if (product == null)
                throw new ArgumentNullException(nameof(product), $&quot;{nameof(product)} is null.&quot;);

            using (var con = OpenConnection())
                UpdateProduct(con, null, product);
        }

        public void UpdateGraph(ProductLine productLine)
        {
            if (productLine == null)
                throw new ArgumentNullException(nameof(productLine), $&quot;{nameof(productLine)} is null.&quot;);

            productLine.ApplyKeys();

            using (var con = OpenConnection())
            using (var trans = con.BeginTransaction())
            {
                UpdateProductLine(con, trans, productLine);
                foreach (var item in productLine.Products)
                {
                    if (item.ProductKey == 0)
                        InsertProduct(con, trans, item);
                    else
                        UpdateProduct(con, trans, item);
                }

                trans.Commit();
            }
        }

        public void UpdateGraphWithChildDeletes(ProductLine productLine)
        {
            if (productLine == null)
                throw new ArgumentNullException(nameof(productLine), $&quot;{nameof(productLine)} is null.&quot;);

            productLine.ApplyKeys();

            using (var con = OpenConnection())
            using (var trans = con.BeginTransaction())
            {
                //Find products to remove
                var originalProductKeys = GetProductKeys(con, trans, productLine.ProductLineKey);
                foreach (var item in productLine.Products)
                    originalProductKeys.Remove(item.ProductKey);

                UpdateProductLine(con, trans, productLine);
                foreach (var item in productLine.Products)
                {
                    if (item.ProductKey == 0)
                        InsertProduct(con, trans, item);
                    else
                        UpdateProduct(con, trans, item);
                }

                //Remove products
                foreach (var key in originalProductKeys)
                    DeleteProduct(con, trans, key);

                trans.Commit();
            }
        }

        public void UpdateGraphWithDeletes(ProductLine productLine, IList&lt;int&gt; productKeysToRemove)
        {
            if (productLine == null)
                throw new ArgumentNullException(nameof(productLine), $&quot;{nameof(productLine)} is null.&quot;);

            productLine.ApplyKeys();

            using (var con = OpenConnection())
            using (var trans = con.BeginTransaction())
            {
                UpdateProductLine(con, trans, productLine);

                foreach (var item in productLine.Products)
                {
                    if (item.ProductKey == 0)
                        InsertProduct(con, trans, item);
                    else
                        UpdateProduct(con, trans, item);
                }

                if (productKeysToRemove != null)
                    foreach (var key in productKeysToRemove)
                        DeleteProduct(con, trans, key);

                trans.Commit();
            }
        }

        static void DeleteProduct(SqlConnection con, SqlTransaction trans, int productKey)
        {
            const string sql = &quot;DELETE Production.Product WHERE ProductKey = @ProductKey;&quot;;

            using (var cmd = new SqlCommand(sql, con, trans))
            {
                cmd.Parameters.AddWithValue(&quot;@ProductKey&quot;, productKey);
                cmd.ExecuteNonQuery();
            }
        }

        static HashSet&lt;int&gt; GetProductKeys(SqlConnection con, SqlTransaction trans, int productLineKey)
        {
            const string sql = &quot;SELECT p.ProductKey FROM Production.Product p WHERE p.ProductLineKey = @ProductLineKey&quot;;

            var results = new HashSet&lt;int&gt;();
            using (var cmd = new SqlCommand(sql, con, trans))
            {
                cmd.Parameters.AddWithValue(&quot;@ProductLineKey&quot;, productLineKey);
                using (var reader = cmd.ExecuteReader())
                    while (reader.Read())
                        results.Add(reader.GetInt32(0));
            }
            return results;
        }

        static void InsertProduct(SqlConnection con, SqlTransaction trans, Product product)
        {
            const string sql = &quot;INSERT INTO Production.Product ( ProductName, ProductLineKey, ShippingWeight, ProductWeight ) OUTPUT Inserted.ProductKey VALUES ( @ProductName, @ProductLineKey, @ShippingWeight, @ProductWeight )&quot;;

            using (var cmd = new SqlCommand(sql, con, trans))
            {
                cmd.Parameters.AddWithValue(&quot;@ProductName&quot;, product.ProductName);
                cmd.Parameters.AddWithValue(&quot;@ProductLineKey&quot;, product.ProductLineKey);
                cmd.Parameters.AddWithValue(&quot;@ShippingWeight&quot;, product.ShippingWeight);
                cmd.Parameters.AddWithValue(&quot;@ProductWeight&quot;, product.ProductWeight);
                product.ProductKey = (int)cmd.ExecuteScalar();
            }
        }

        static void UpdateProduct(SqlConnection con, SqlTransaction? trans, Product product)
        {
            const string sql = &quot;UPDATE Production.Product SET ProductName = @ProductName, ProductLineKey = @ProductLineKey, ShippingWeight = @ShippingWeight, ProductWeight = @ProductWeight WHERE ProductKey = @ProductKey;&quot;;

            using (var cmd = new SqlCommand(sql, con, trans))
            {
                cmd.Parameters.AddWithValue(&quot;@ProductKey&quot;, product.ProductKey);
                cmd.Parameters.AddWithValue(&quot;@ProductName&quot;, product.ProductName);
                cmd.Parameters.AddWithValue(&quot;@ProductLineKey&quot;, product.ProductLineKey);
                cmd.Parameters.AddWithValue(&quot;@ShippingWeight&quot;, product.ShippingWeight);
                cmd.Parameters.AddWithValue(&quot;@ProductWeight&quot;, product.ProductWeight);

                cmd.ExecuteNonQuery();
            }
        }

        static void UpdateProductLine(SqlConnection con, SqlTransaction? trans, ProductLine productLine)
        {
            const string sql = &quot;UPDATE Production.ProductLine SET ProductLineName = @ProductLineName WHERE ProductLineKey = @ProductLineKey;&quot;;

            using (var cmd = new SqlCommand(sql, con, trans))
            {
                cmd.Parameters.AddWithValue(&quot;@ProductLineKey&quot;, productLine.ProductLineKey);
                cmd.Parameters.AddWithValue(&quot;@ProductLineName&quot;, productLine.ProductLineName);
                cmd.ExecuteNonQuery();
            }
        }
    }
</code></pre>

<h2 id="chain">Chain<a class="headerlink" href="#chain" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<p>Chain requires operating on the parent and child objects separately.</p>
<pre><code class="cs">public class ModelWithChildrenScenario : IModelWithChildrenScenario&lt;ProductLine, Product&gt;
{
    readonly SqlServerDataSource m_DataSource;
    readonly string ProductTable = &quot;Production.Product&quot;;

    public ModelWithChildrenScenario(SqlServerDataSource dataSource)
    {
        m_DataSource = dataSource;
    }

    public int Create(ProductLine productLine)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $&quot;{nameof(productLine)} is null.&quot;);

        using (var trans = m_DataSource.BeginTransaction())
        {
            productLine.ProductLineKey = trans.Insert(productLine).ToInt32().Execute();
            productLine.ApplyKeys();
            trans.InsertBatch(productLine.Products).Execute();
            trans.Commit();
        }

        return productLine.ProductLineKey;
    }

    public void Delete(ProductLine productLine)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $&quot;{nameof(productLine)} is null.&quot;);

        using (var trans = m_DataSource.BeginTransaction())
        {
            trans.DeleteSet&lt;Product&gt;(new { productLine.ProductLineKey }).Execute();
            trans.Delete(productLine).Execute();
            trans.Commit();
        }
    }

    public void DeleteByKey(int productLineKey)
    {
        using (var trans = m_DataSource.BeginTransaction())
        {
            trans.DeleteSet&lt;Product&gt;(new { productLineKey }).Execute();
            trans.DeleteByKey&lt;ProductLine&gt;(productLineKey).Execute();
            trans.Commit();
        }
    }

    public IList&lt;ProductLine&gt; FindByName(string productLineName, bool includeProducts)
    {
        var results = m_DataSource.From&lt;ProductLine&gt;(new { productLineName }).ToCollection().Execute();
        if (results.Count &gt; 0 &amp;&amp; includeProducts)
        {
            var children = m_DataSource.GetByColumnList&lt;Product&gt;(&quot;ProductLineKey&quot;,
                results.Select(pl =&gt; pl.ProductLineKey)).ToCollection().Execute();
            foreach (var line in results)
                line.Products.AddRange(children.Where(x =&gt; x.ProductLineKey == line.ProductLineKey));
        }
        return results;
    }

    public IList&lt;ProductLine&gt; GetAll(bool includeProducts)
    {
        var results = m_DataSource.From&lt;ProductLine&gt;().ToCollection().Execute();
        if (includeProducts)
        {
            var children = m_DataSource.From&lt;Product&gt;().ToCollection().Execute();
            foreach (var line in results)
                line.Products.AddRange(children.Where(x =&gt; x.ProductLineKey == line.ProductLineKey));
        }
        return results;
    }

    public ProductLine? GetByKey(int productLineKey, bool includeProducts)
    {
        var result = m_DataSource.GetByKey&lt;ProductLine&gt;(productLineKey).ToObjectOrNull().Execute();
        if (result != null &amp;&amp; includeProducts)
        {
            var children = m_DataSource.From&lt;Product&gt;(new { result.ProductLineKey }).ToCollection().Execute();
            result.Products.AddRange(children);
        }
        return result;
    }

    public void Update(Product product)
    {
        if (product == null)
            throw new ArgumentNullException(nameof(product), $&quot;{nameof(product)} is null.&quot;);

        m_DataSource.Update(product).Execute();
    }

    public void Update(ProductLine productLine)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $&quot;{nameof(productLine)} is null.&quot;);

        m_DataSource.Update(productLine).Execute();
    }

    public void UpdateGraph(ProductLine productLine)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $&quot;{nameof(productLine)} is null.&quot;);

        using (var trans = m_DataSource.BeginTransaction())
        {
            //Update parent row
            trans.Update(productLine).Execute();

            //Ensure new child rows have their parent's key
            productLine.ApplyKeys();

            //Insert/update the remaining child rows
            foreach (var row in productLine.Products)
                trans.Upsert(row).Execute();

            trans.Commit();
        }
    }

    public void UpdateGraphWithChildDeletes(ProductLine productLine)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $&quot;{nameof(productLine)} is null.&quot;);

        using (var trans = m_DataSource.BeginTransaction())
        {
            //Update parent row
            trans.Update(productLine).Execute();

            //Find the list of child keys to remove
            var oldKeys = trans.From&lt;Product&gt;(new { productLine.ProductLineKey }).ToInt32List(&quot;ProductKey&quot;)
                .Execute().ToHashSet();

            foreach (var key in productLine.Products.Select(x =&gt; x.ProductKey))
                oldKeys.Remove(key);

            //Remove the old records
            foreach (var key in oldKeys)
                trans.DeleteByKey&lt;Product&gt;(key).Execute();

            //Ensure new child rows have their parent's key
            productLine.ApplyKeys();

            //Insert/update the child rows
            foreach (var row in productLine.Products)
                trans.Upsert(row).Execute();

            trans.Commit();
        }
    }

    public void UpdateGraphWithDeletes(ProductLine productLine, IList&lt;int&gt; productKeysToRemove)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $&quot;{nameof(productLine)} is null.&quot;);

        using (var trans = m_DataSource.BeginTransaction())
        {
            //Update parent row
            trans.Update(productLine).Execute();

            //Ensure new child rows have their parent's key
            productLine.ApplyKeys();

            //Insert/update the child rows
            foreach (var row in productLine.Products)
                trans.Upsert(row).Execute();

            if (productKeysToRemove?.Count &gt; 0)
                trans.DeleteByKeyList(ProductTable, productKeysToRemove).Execute();

            trans.Commit();
        }
    }
}
</code></pre>

<h2 id="dapper">Dapper<a class="headerlink" href="#dapper" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<pre><code class="cs">    public class ModelWithChildrenScenario : ScenarioBase, IModelWithChildrenScenario&lt;ProductLine, Product&gt;
    {
        public ModelWithChildrenScenario(string connectionString) : base(connectionString)
        {
        }

        public int Create(ProductLine productLine)
        {
            if (productLine == null)
                throw new ArgumentNullException(nameof(productLine), $&quot;{nameof(productLine)} is null.&quot;);

            const string sql = &quot;INSERT INTO Production.ProductLine ( ProductLineName ) OUTPUT Inserted.ProductLineKey VALUES (@ProductLineName);&quot;;

            using (var con = OpenConnection())
            using (var trans = con.BeginTransaction())
            {
                productLine.ProductLineKey = (int)con.ExecuteScalar(sql, productLine, transaction: trans);
                productLine.ApplyKeys();

                foreach (var item in productLine.Products)
                    InsertProduct(con, trans, item);

                trans.Commit();
            }
            return productLine.ProductLineKey;
        }

        public void Delete(ProductLine productLine)
        {
            if (productLine == null)
                throw new ArgumentNullException(nameof(productLine), $&quot;{nameof(productLine)} is null.&quot;);

            const string sql = @&quot;DELETE FROM Production.Product WHERE ProductLineKey = @ProductLineKey;
DELETE FROM Production.ProductLine WHERE ProductLineKey = @ProductLineKey&quot;;

            using (var con = OpenConnection())
                con.Execute(sql, productLine);
        }

        public void DeleteByKey(int productLineKey)
        {
            const string sql = @&quot;DELETE FROM Production.Product WHERE ProductLineKey = @ProductLineKey;
DELETE FROM Production.ProductLine WHERE ProductLineKey = @ProductLineKey&quot;;

            using (var con = OpenConnection())
                con.Execute(sql, new { productLineKey });
        }

        public IList&lt;ProductLine&gt; FindByName(string productLineName, bool includeProducts)
        {
            const string sqlA = @&quot;SELECT pl.ProductLineKey, pl.ProductLineName FROM Production.ProductLine pl WHERE pl.ProductLineName = @ProductLineName;
SELECT p.ProductKey, p.ProductName, p.ProductLineKey, p.ShippingWeight, p.ProductWeight FROM Production.Product p INNER JOIN Production.ProductLine pl ON p.ProductLineKey = pl.ProductLineKey WHERE pl.ProductLineName = @ProductLineName;&quot;;

            const string sqlB = @&quot;SELECT pl.ProductLineKey, pl.ProductLineName FROM Production.ProductLine pl WHERE pl.ProductLineName = @ProductLineName;&quot;;

            using (var con = OpenConnection())
            {
                var sql = includeProducts ? sqlA : sqlB;
                var results = con.QueryMultiple(sql, new { productLineName });
                var productLines = results.Read&lt;ProductLine&gt;().ToList();

                if (includeProducts)
                {
                    var lookup = productLines.ToDictionary(x =&gt; x.ProductLineKey);
                    foreach (var product in results.Read&lt;Product&gt;())
                        lookup[product.ProductLineKey].Products.Add(product);
                }

                return productLines;
            }
        }

        public IList&lt;ProductLine&gt; GetAll(bool includeProducts)
        {
            const string sqlA = @&quot;SELECT pl.ProductLineKey, pl.ProductLineName FROM Production.ProductLine pl;
SELECT p.ProductKey, p.ProductName, p.ProductLineKey, p.ShippingWeight, p.ProductWeight FROM Production.Product p INNER JOIN Production.ProductLine pl ON p.ProductLineKey = pl.ProductLineKey;&quot;;

            const string sqlB = @&quot;SELECT pl.ProductLineKey, pl.ProductLineName FROM Production.ProductLine pl;&quot;;

            using (var con = OpenConnection())
            {
                var sql = includeProducts ? sqlA : sqlB;
                var results = con.QueryMultiple(sql);
                var productLines = results.Read&lt;ProductLine&gt;().ToList();

                if (includeProducts)
                {
                    var lookup = productLines.ToDictionary(x =&gt; x.ProductLineKey);
                    foreach (var product in results.Read&lt;Product&gt;())
                        lookup[product.ProductLineKey].Products.Add(product);
                }

                return productLines;
            }
        }

        public ProductLine? GetByKey(int productLineKey, bool includeProducts)
        {
            const string sqlA = @&quot;SELECT pl.ProductLineKey, pl.ProductLineName FROM Production.ProductLine pl WHERE pl.ProductLineKey = @ProductLineKey;
SELECT p.ProductKey, p.ProductName, p.ProductLineKey, p.ShippingWeight, p.ProductWeight FROM Production.Product p WHERE p.ProductLineKey = @ProductLineKey;&quot;;

            const string sqlB = @&quot;SELECT pl.ProductLineKey, pl.ProductLineName FROM Production.ProductLine pl WHERE pl.ProductLineKey = @ProductLineKey;&quot;;

            using (var con = OpenConnection())
            {
                var sql = includeProducts ? sqlA : sqlB;
                var results = con.QueryMultiple(sql, new { productLineKey });
                var productLine = results.ReadSingleOrDefault&lt;ProductLine&gt;();

                if (productLine == null)
                    return null;

                if (includeProducts)
                    foreach (var product in results.Read&lt;Product&gt;())
                        productLine.Products.Add(product);

                return productLine;
            }
        }

        public void Update(ProductLine productLine)
        {
            if (productLine == null)
                throw new ArgumentNullException(nameof(productLine), $&quot;{nameof(productLine)} is null.&quot;);

            using (var con = OpenConnection())
                UpdateProductLine(con, null, productLine);
        }

        public void Update(Product product)
        {
            if (product == null)
                throw new ArgumentNullException(nameof(product), $&quot;{nameof(product)} is null.&quot;);

            using (var con = OpenConnection())
                UpdateProduct(con, null, product);
        }

        public void UpdateGraph(ProductLine productLine)
        {
            if (productLine == null)
                throw new ArgumentNullException(nameof(productLine), $&quot;{nameof(productLine)} is null.&quot;);

            productLine.ApplyKeys();

            using (var con = OpenConnection())
            using (var trans = con.BeginTransaction())
            {
                UpdateProductLine(con, trans, productLine);
                foreach (var item in productLine.Products)
                {
                    if (item.ProductKey == 0)
                        InsertProduct(con, trans, item);
                    else
                        UpdateProduct(con, trans, item);
                }
                trans.Commit();
            }
        }

        public void UpdateGraphWithChildDeletes(ProductLine productLine)
        {
            if (productLine == null)
                throw new ArgumentNullException(nameof(productLine), $&quot;{nameof(productLine)} is null.&quot;);

            productLine.ApplyKeys();

            using (var con = OpenConnection())
            using (var trans = con.BeginTransaction())
            {
                //Find products to remove
                var originalProductKeys = GetProductKeys(con, trans, productLine.ProductLineKey);
                foreach (var item in productLine.Products)
                    originalProductKeys.Remove(item.ProductKey);

                UpdateProductLine(con, trans, productLine);
                foreach (var item in productLine.Products)
                {
                    if (item.ProductKey == 0)
                        InsertProduct(con, trans, item);
                    else
                        UpdateProduct(con, trans, item);
                }

                //Remove products
                foreach (var key in originalProductKeys)
                    DeleteProduct(con, trans, key);

                trans.Commit();
            }
        }

        public void UpdateGraphWithDeletes(ProductLine productLine, IList&lt;int&gt; productKeysToRemove)
        {
            if (productLine == null)
                throw new ArgumentNullException(nameof(productLine), $&quot;{nameof(productLine)} is null.&quot;);

            productLine.ApplyKeys();

            using (var con = OpenConnection())
            using (var trans = con.BeginTransaction())
            {
                UpdateProductLine(con, trans, productLine);

                foreach (var item in productLine.Products)
                {
                    if (item.ProductKey == 0)
                        InsertProduct(con, trans, item);
                    else
                        UpdateProduct(con, trans, item);
                }

                if (productKeysToRemove != null)
                    foreach (var key in productKeysToRemove)
                        DeleteProduct(con, trans, key);

                trans.Commit();
            }
        }

        static void DeleteProduct(SqlConnection con, SqlTransaction trans, int productKey)
        {
            const string sql = &quot;DELETE Production.Product WHERE ProductKey = @ProductKey;&quot;;

            con.Execute(sql, new { productKey }, transaction: trans);
        }

        static HashSet&lt;int&gt; GetProductKeys(SqlConnection con, SqlTransaction trans, int productLineKey)
        {
            const string sql = &quot;SELECT p.ProductKey FROM Production.Product p WHERE p.ProductLineKey = @ProductLineKey&quot;;

            return con.Query&lt;int&gt;(sql, new { productLineKey }, transaction: trans).ToHashSet();
        }

        static void InsertProduct(SqlConnection con, SqlTransaction trans, Product product)
        {
            const string sql = &quot;INSERT INTO Production.Product ( ProductName, ProductLineKey, ShippingWeight, ProductWeight ) OUTPUT Inserted.ProductKey VALUES ( @ProductName, @ProductLineKey, @ShippingWeight, @ProductWeight )&quot;;

            product.ProductKey = con.ExecuteScalar&lt;int&gt;(sql, product, transaction: trans);
        }

        static void UpdateProduct(SqlConnection con, SqlTransaction? trans, Product product)
        {
            const string sql = &quot;UPDATE Production.Product SET ProductName = @ProductName, ProductLineKey = @ProductLineKey, ShippingWeight = @ShippingWeight, ProductWeight = @ProductWeight WHERE ProductKey = @ProductKey;&quot;;

            con.Execute(sql, product, transaction: trans);
        }

        static void UpdateProductLine(SqlConnection con, SqlTransaction? trans, ProductLine productLine)
        {
            const string sql = &quot;UPDATE Production.ProductLine SET ProductLineName = @ProductLineName WHERE ProductLineKey = @ProductLineKey;&quot;;

            con.Execute(sql, productLine, transaction: trans);
        }
    }
</code></pre>

<h2 id="dbconnector">DbConnector<a class="headerlink" href="#dbconnector" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<pre><code class="cs">public class ModelWithChildrenScenario : ScenarioBase, IModelWithChildrenScenario&lt;ProductLine, Product&gt;
{
    public ModelWithChildrenScenario(string connectionString) : base(connectionString)
    {
    }

    public int Create(ProductLine productLine)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $&quot;{nameof(productLine)} is null.&quot;);

        const string sql = &quot;INSERT INTO Production.ProductLine ( ProductLineName ) OUTPUT Inserted.ProductLineKey VALUES (@ProductLineName);&quot;;

        //Build the main IDbJob.
        IDbJob&lt;int&gt; jobProductLine = DbConnector
            .Scalar&lt;int&gt;(sql, productLine)
            .OnExecuted((int result, IDbExecutedModel im) =&gt;
            {
                productLine.ProductLineKey = result;
                productLine.ApplyKeys();
                return result;
            });

        if (productLine.Products.Count == 0)
        {
            return jobProductLine.Execute();
        }
        else
        {
            //Leverage DbConnector's Job Batching feature
            DbJob.ExecuteAll(jobProductLine, BuildInsertOrUpdateProducts(productLine.Products));

            return productLine.ProductLineKey;
        }
    }

    public void Delete(ProductLine productLine)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $&quot;{nameof(productLine)} is null.&quot;);

        const string sql = @&quot;DELETE FROM Production.Product WHERE ProductLineKey = @ProductLineKey;
        DELETE FROM Production.ProductLine WHERE ProductLineKey = @ProductLineKey&quot;;

        DbConnector.NonQuery(sql, productLine).Execute();
    }

    public void DeleteByKey(int productLineKey)
    {
        const string sql = @&quot;DELETE FROM Production.Product WHERE ProductLineKey = @ProductLineKey;
        DELETE FROM Production.ProductLine WHERE ProductLineKey = @ProductLineKey&quot;;

        DbConnector.NonQuery(sql, new { productLineKey }).Execute();
    }

    public IList&lt;ProductLine&gt; FindByName(string productLineName, bool includeProducts)
    {
        if (includeProducts)
        {
            const string sqlA = @&quot;
            SELECT 
                pl.ProductLineKey, 
                pl.ProductLineName 
            FROM Production.ProductLine pl 
            WHERE pl.ProductLineName = @ProductLineName;
            SELECT 
                p.ProductKey, 
                p.ProductName, 
                p.ProductLineKey, 
                p.ShippingWeight, 
                p.ProductWeight 
            FROM Production.Product p 
            INNER JOIN Production.ProductLine pl ON p.ProductLineKey = pl.ProductLineKey 
            WHERE pl.ProductLineName = @ProductLineName;&quot;;

            var tupleResult = DbConnector.ReadToList&lt;ProductLine, Product&gt;(sqlA, new { productLineName }).Execute();

            List&lt;ProductLine&gt; productLines = tupleResult.Item1;
            List&lt;Product&gt; products = tupleResult.Item2;

            productLines.ForEach(pl =&gt; pl.Products.AddRange(products.Where(p =&gt; p.ProductLineKey == pl.ProductLineKey)));

            return productLines;
        }
        else
        {
            const string sqlB = @&quot;
            SELECT 
                pl.ProductLineKey, 
                pl.ProductLineName 
            FROM Production.ProductLine pl 
            WHERE pl.ProductLineName = @ProductLineName;&quot;;

            return DbConnector.ReadToList&lt;ProductLine&gt;(sqlB, new { productLineName }).Execute();
        }
    }

    public IList&lt;ProductLine&gt; GetAll(bool includeProducts)
    {
        if (includeProducts)
        {
            const string sqlA = @&quot;
            SELECT 
                pl.ProductLineKey, 
                pl.ProductLineName 
            FROM Production.ProductLine pl;
            SELECT 
                p.ProductKey, 
                p.ProductName, 
                p.ProductLineKey, 
                p.ShippingWeight, 
                p.ProductWeight 
            FROM Production.Product p 
            INNER JOIN Production.ProductLine pl ON p.ProductLineKey = pl.ProductLineKey;&quot;;

            var tupleResult = DbConnector.ReadToList&lt;ProductLine, Product&gt;(sqlA).Execute();

            List&lt;ProductLine&gt; productLines = tupleResult.Item1;
            List&lt;Product&gt; products = tupleResult.Item2;

            productLines.ForEach(pl =&gt; pl.Products.AddRange(products.Where(p =&gt; p.ProductLineKey == pl.ProductLineKey)));

            return productLines;
        }
        else
        {
            const string sqlB = @&quot;
            SELECT 
                pl.ProductLineKey, 
                pl.ProductLineName 
            FROM Production.ProductLine pl;&quot;;

            return DbConnector.ReadToList&lt;ProductLine&gt;(sqlB).Execute();
        }
    }

    public ProductLine? GetByKey(int productLineKey, bool includeProducts)
    {
        if (includeProducts)
        {
            const string sqlA = @&quot;
            SELECT 
                pl.ProductLineKey, 
                pl.ProductLineName 
            FROM Production.ProductLine pl
            WHERE pl.ProductLineKey = @ProductLineKey;
            SELECT 
                p.ProductKey, 
                p.ProductName, 
                p.ProductLineKey, 
                p.ShippingWeight, 
                p.ProductWeight 
            FROM Production.Product p 
            INNER JOIN Production.ProductLine pl ON p.ProductLineKey = pl.ProductLineKey
            WHERE p.ProductLineKey = @productLineKey;&quot;;

            var tupleResult = DbConnector.ReadToList&lt;ProductLine, Product&gt;(sqlA, new { productLineKey }).Execute();

            ProductLine productLine = tupleResult.Item1.FirstOrDefault();

            if (productLine != null)
            {
                productLine.Products.AddRange(tupleResult.Item2);
            }

            return productLine;
        }
        else
        {
            const string sqlB = @&quot;
            SELECT 
                pl.ProductLineKey, 
                pl.ProductLineName 
            FROM Production.ProductLine pl
            WHERE pl.ProductLineKey = @productLineKey;&quot;;

            return DbConnector.ReadFirstOrDefault&lt;ProductLine&gt;(sqlB, new { productLineKey }).Execute();
        }
    }

    public void Update(ProductLine productLine)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $&quot;{nameof(productLine)} is null.&quot;);

        BuildUpdateProductLine(productLine).Execute();
    }

    public void Update(Product product)
    {
        if (product == null)
            throw new ArgumentNullException(nameof(product), $&quot;{nameof(product)} is null.&quot;);

        BuildUpdateProduct(product).Execute();
    }

    public void UpdateGraph(ProductLine productLine)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $&quot;{nameof(productLine)} is null.&quot;);

        productLine.ApplyKeys();

        //Leverage DbConnector's Job Batching feature
        DbJob.ExecuteAll(BuildUpdateProductLine(productLine), BuildInsertOrUpdateProducts(productLine.Products));
    }

    public void UpdateGraphWithChildDeletes(ProductLine productLine)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $&quot;{nameof(productLine)} is null.&quot;);

        productLine.ApplyKeys();

        //Note: This could all be done in a single query transaction leveraging the MERGE feature
        using (var con = OpenConnection())
        using (var trans = con.BeginTransaction())
        {
            //Find products
            var originalProductKeys = BuildGetProductKeys(productLine.ProductLineKey).Execute(trans);

            foreach (var item in productLine.Products)
                originalProductKeys.Remove(item.ProductKey);

            //Leverage DbConnector's Job Batching feature
            DbJob.ExecuteAll(trans, BuildUpdateProductLine(productLine), BuildInsertOrUpdateProducts(productLine.Products));

            //Remove products
            if (originalProductKeys.Count &gt; 0)
                BuildDeleteProducts(originalProductKeys).Execute(trans);

            trans.Commit();
        }
    }

    public void UpdateGraphWithDeletes(ProductLine productLine, IList&lt;int&gt; productKeysToRemove)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $&quot;{nameof(productLine)} is null.&quot;);

        productLine.ApplyKeys();

        if (productKeysToRemove == null || productKeysToRemove.Count == 0)
        {
            DbJob.ExecuteAll(BuildUpdateProductLine(productLine), BuildInsertOrUpdateProducts(productLine.Products));
        }
        else
        {
            DbJob.ExecuteAll(BuildUpdateProductLine(productLine), BuildInsertOrUpdateProducts(productLine.Products), BuildDeleteProducts(productKeysToRemove));
        }
    }

    protected IDbJob&lt;int?&gt; BuildDeleteProduct(int productKey)
    {
        const string sql = &quot;DELETE Production.Product WHERE ProductKey = @productKey;&quot;;

        return DbConnector.NonQuery(sql, new { productKey });
    }

    protected IDbJob&lt;int?&gt; BuildDeleteProducts(IEnumerable&lt;int&gt; productKeys)
    {
        if (productKeys == null || !productKeys.Any())
            throw new ArgumentException($&quot;{nameof(productKeys)} is null or empty.&quot;, nameof(productKeys));

        var keyList = string.Join(&quot;, &quot;, productKeys);
        var sql = $&quot;DELETE Production.Product WHERE ProductKey IN ({keyList});&quot;;

        return DbConnector.NonQuery(sql);
    }

    protected IDbJob&lt;HashSet&lt;int&gt;&gt; BuildGetProductKeys(int productLineKey)
    {
        const string sql = &quot;SELECT p.ProductKey FROM Production.Product p WHERE p.ProductLineKey = @productLineKey&quot;;

        return DbConnector.ReadToHashSet&lt;int&gt;(sql, new { productLineKey });
    }

    protected IDbJob&lt;int&gt; BuildInsertProduct(Product product)
    {
        const string sql = &quot;INSERT INTO Production.Product ( ProductName, ProductLineKey, ShippingWeight, ProductWeight ) OUTPUT Inserted.ProductKey VALUES ( @ProductName, @ProductLineKey, @ShippingWeight, @ProductWeight )&quot;;

        return DbConnector.Scalar&lt;int&gt;(sql, product).OnCompleted(result =&gt; { product.ProductKey = result; return result; });
    }

    protected IDbJob&lt;int?&gt; BuildInsertOrUpdateProducts(IEnumerable&lt;Product&gt; products)
    {
        if (products == null || !products.Any())
            throw new ArgumentException($&quot;{nameof(products)} is null or empty.&quot;, nameof(products));

        string sql = $@&quot;
            MERGE INTO {Product.TableName} target
            USING
            (
                VALUES (
                    @{nameof(Product.ProductKey)},
                    @{nameof(Product.ProductName)},
                    @{nameof(Product.ProductLineKey)},
                    @{nameof(Product.ShippingWeight)},
                    @{nameof(Product.ProductWeight)}
                )
            ) source (
                        {nameof(Product.ProductKey)},
                        {nameof(Product.ProductName)},
                        {nameof(Product.ProductLineKey)},
                        {nameof(Product.ShippingWeight)},
                        {nameof(Product.ProductWeight)}
                     )
            ON target.ProductKey = source.{nameof(Product.ProductKey)}
            WHEN MATCHED THEN
                UPDATE SET ProductName = source.{nameof(Product.ProductName)},
                           ProductLineKey = source.{nameof(Product.ProductLineKey)},
                           ShippingWeight = source.{nameof(Product.ShippingWeight)},
                           ProductWeight = source.{nameof(Product.ProductWeight)}
            WHEN NOT MATCHED THEN
                INSERT
                (
                    ProductName,
                    ProductLineKey,
                    ShippingWeight,
                    ProductWeight
                )
                VALUES
                (
                    source.{nameof(Product.ProductName)},
                    source.{nameof(Product.ProductLineKey)},
                    source.{nameof(Product.ShippingWeight)},
                    source.{nameof(Product.ProductWeight)}
                )
            OUTPUT Inserted.ProductKey;&quot;;

        Product firstProd = products.First();

        //Best approach for unlimited inserts since SQL server has parameter amount restrictions
        //https://docs.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?redirectedfrom=MSDN&amp;view=sql-server-ver15
        return DbConnector.Build&lt;int?&gt;(
                sql: sql,
                param: firstProd,
                onExecute: (int? result, IDbExecutionModel em) =&gt;
                {
                    //Set the command
                    DbCommand command = em.Command;

                    //Execute first row.
                    firstProd.ProductKey = (int)command.ExecuteScalar()!;
                    em.NumberOfRowsAffected = 1;

                    //Set and execute remaining rows.
                    foreach (var prod in products.Skip(1))
                    {
                        command.Parameters[nameof(Product.ProductKey)].Value = prod.ProductKey;
                        command.Parameters[nameof(Product.ProductName)].Value = prod.ProductName ?? (object)DBNull.Value;
                        command.Parameters[nameof(Product.ProductLineKey)].Value = prod.ProductLineKey;
                        command.Parameters[nameof(Product.ShippingWeight)].Value = prod.ShippingWeight ?? (object)DBNull.Value;
                        command.Parameters[nameof(Product.ProductWeight)].Value = prod.ProductWeight ?? (object)DBNull.Value;

                        prod.ProductKey = (int)command.ExecuteScalar()!;
                        em.NumberOfRowsAffected += 1;
                    }

                    return em.NumberOfRowsAffected;
                }
            )
            .WithIsolationLevel(IsolationLevel.ReadCommitted);//Use a transaction
    }

    protected IDbJob&lt;int?&gt; BuildUpdateProduct(Product product)
    {
        const string sql = &quot;UPDATE Production.Product SET ProductName = @ProductName, ProductLineKey = @ProductLineKey, ShippingWeight = @ShippingWeight, ProductWeight = @ProductWeight WHERE ProductKey = @ProductKey;&quot;;

        return DbConnector.NonQuery(sql, product);
    }

    protected IDbJob&lt;int?&gt; BuildUpdateProductLine(ProductLine productLine)
    {
        const string sql = &quot;UPDATE Production.ProductLine SET ProductLineName = @ProductLineName WHERE ProductLineKey = @ProductLineKey;&quot;;

        return DbConnector.NonQuery(sql, productLine);
    }
}
</code></pre>

<h2 id="entity-framework-6">Entity Framework 6<a class="headerlink" href="#entity-framework-6" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<p>TODO</p>
<h2 id="entity-framework-core">Entity Framework Core<a class="headerlink" href="#entity-framework-core" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<p>When performing an update, ensure that the <code>EntityState</code> is correctly set for each child record based on whether it is an insert or update.</p>
<p>For partial deletes, you have to explicitly find and delete the child rows that are no longer needed.</p>
<p>For deletes, ensure the child record in DBContext is setup with <code>.OnDelete(DeleteBehavior.Cascade)</code></p>
<pre><code class="cs">public class ModelWithChildrenScenario : IModelWithChildrenScenario&lt;ProductLine, Product&gt;
{
    private Func&lt;OrmCookbookContext&gt; CreateDbContext;

    public ModelWithChildrenScenario(Func&lt;OrmCookbookContext&gt; dBContextFactory)
    {
        CreateDbContext = dBContextFactory;
    }

    public int Create(ProductLine productLine)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $&quot;{nameof(productLine)} is null.&quot;);

        //A transaction is automatically created when `SaveChanges()` is called.
        using (var context = CreateDbContext())
        {
            context.ProductLines.Add(productLine);
            context.SaveChanges();
            return productLine.ProductLineKey;
        }
    }

    public void Delete(ProductLine productLine)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $&quot;{nameof(productLine)} is null.&quot;);

        using (var context = CreateDbContext())
        {
            context.ProductLines.Remove(productLine);
            context.SaveChanges();
        }
    }

    public void DeleteByKey(int productLineKey)
    {
        using (var context = CreateDbContext())
        {
            //Need to explicitly fetch child records in order to delete them.
            var temp = context.ProductLines.Where(x =&gt; x.ProductLineKey == productLineKey).Include(x =&gt; x.Products).Single();
            context.ProductLines.Remove(temp);
            context.SaveChanges();
        }
    }

    public IList&lt;ProductLine&gt; FindByName(string productLineName, bool includeProducts)
    {
        using (var context = CreateDbContext())
        {
            if (includeProducts)
                return context.ProductLines.Where(x =&gt; x.ProductLineName == productLineName).Include(x =&gt; x.Products).ToList();
            else
                return context.ProductLines.Where(x =&gt; x.ProductLineName == productLineName).ToList();
        }
    }

    public IList&lt;ProductLine&gt; GetAll(bool includeProducts)
    {
        using (var context = CreateDbContext())
        {
            if (includeProducts)
                return context.ProductLines.Include(x =&gt; x.Products).ToList();
            else
                return context.ProductLines.ToList();
        }
    }

    public ProductLine? GetByKey(int productLineKey, bool includeProducts)
    {
        using (var context = CreateDbContext())
        {
            if (includeProducts)
                return context.ProductLines.Where(x =&gt; x.ProductLineKey == productLineKey).Include(x =&gt; x.Products).SingleOrDefault();
            else
                return context.ProductLines.Find(productLineKey);
        }
    }

    public void Update(Product product)
    {
        if (product == null)
            throw new ArgumentNullException(nameof(product), $&quot;{nameof(product)} is null.&quot;);

        using (var context = CreateDbContext())
        {
            context.Entry(product).State = EntityState.Modified;
            context.SaveChanges();
        }
    }

    public void Update(ProductLine productLine)
    {
        using (var context = CreateDbContext())
        {
            context.Entry(productLine).State = EntityState.Modified;
            context.SaveChanges();
        }
    }

    public void UpdateGraph(ProductLine productLine)
    {
        //A transaction is automatically created when `SaveChanges()` is called.
        using (var context = CreateDbContext())
        {
            context.Entry(productLine).State = EntityState.Modified;

            foreach (var item in productLine.Products)
                if (item.ProductKey == 0)
                    context.Entry(item).State = EntityState.Added;
                else
                    context.Entry(item).State = EntityState.Modified;

            context.SaveChanges();
        }
    }

    public void UpdateGraphWithChildDeletes(ProductLine productLine)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $&quot;{nameof(productLine)} is null.&quot;);

        //An explicit transaction is needed reading the rows to delete happens outside of the `SaveChanges` call.
        using (var context = CreateDbContext())
        using (var transaction = context.Database.BeginTransaction())
        {
            var validKeys = productLine.Products.Select(x =&gt; x.ProductKey).ToList();

            //get rows to delete
            var oldRows = context.Products.Where(x =&gt; x.ProductLineKey == productLine.ProductLineKey &amp;&amp; !validKeys.Contains(x.ProductKey)).ToList();

            //Remove the old records
            foreach (var row in oldRows)
                context.Products.Remove(row);

            context.Entry(productLine).State = EntityState.Modified;
            foreach (var item in productLine.Products)
                if (item.ProductKey == 0)
                    context.Entry(item).State = EntityState.Added;
                else
                    context.Entry(item).State = EntityState.Modified;
            context.SaveChanges();

            transaction.Commit();
        }
    }

    public void UpdateGraphWithDeletes(ProductLine productLine, IList&lt;int&gt; productKeysToRemove)
    {
        //A transaction is automatically created when `SaveChanges()` is called.
        using (var context = CreateDbContext())
        {
            context.Entry(productLine).State = EntityState.Modified;
            foreach (var item in productLine.Products)
                if (item.ProductKey == 0)
                    context.Entry(item).State = EntityState.Added;
                else
                    context.Entry(item).State = EntityState.Modified;

            if (productKeysToRemove != null)
                foreach (var key in productKeysToRemove)
                    context.Entry(new Product() { ProductKey = key }).State = EntityState.Deleted;

            context.SaveChanges();
        }
    }
}
</code></pre>

<h2 id="linq-to-db">LINQ to DB<a class="headerlink" href="#linq-to-db" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<p>TODO</p>
<h2 id="llblgen-pro">LLBLGen Pro<a class="headerlink" href="#llblgen-pro" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<p>With LLBLGen Pro we can utilize straightforward ORM functionality with this, utilizing the stand-alone, database agnostic Unit of Work
class for easy transaction management and order-of-operation control. For the partial deletes two variants are implemented, one
with a removal tracker, which tracks which entities are removed and which can be deleted in one go (the <code>Update</code> method in the alt repository), 
and one with a direct delete on the table using a not-in predicate, available in the regular <code>Update</code> method. </p>
<p>Nested deletes are implemented explicitly, as cascading deletes aren't supported at the ORM level; to have cascading deletes you 
have to set up the deletes as cascading on the foreign key constraint. </p>
<pre><code class="cs"> public class ModelWithChildrenScenario : IModelWithChildrenScenario&lt;ProductLineEntity, ProductEntity&gt;
 {
     public int Create(ProductLineEntity productLine)
     {
         if (productLine == null)
             throw new ArgumentNullException(nameof(productLine), $&quot;{nameof(productLine)} is null.&quot;);

         using (var adapter = new DataAccessAdapter())
         {
             adapter.SaveEntity(productLine);
             return productLine.ProductLineKey;
         }
     }

     public void Delete(ProductLineEntity productLine)
     {
         if (productLine == null)
             throw new ArgumentNullException(nameof(productLine), $&quot;{nameof(productLine)} is null.&quot;);

         // We'll have to delete the whole graph so first the related entities, then the main entity.
         // Let's use a Unit of work here for that.
         var uow = new UnitOfWork2();
         uow.AddCollectionForDelete(productLine.Products);
         uow.AddForDelete(productLine);
         using (var adapter = new DataAccessAdapter())
         {
             uow.Commit(adapter);
         }
     }

     public void DeleteByKey(int productLineKey)
     {
         // let's directly delete the entities, without fetching them. Use a unit of work for this
         // to wrap everything neatly in a transaction when it's committed. A Unit of work is a
         // persistence agnostic object you can pass on freely to add work and then have all the work
         // performed in a single transaction.
         var uow = new UnitOfWork2();
         uow.AddDeleteEntitiesDirectlyCall(typeof(ProductEntity), 
                                  new RelationPredicateBucket(ProductFields.ProductLineKey.Equal(productLineKey)));
         uow.AddDeleteEntitiesDirectlyCall(typeof(ProductLineEntity), 
                                  new RelationPredicateBucket(ProductLineFields.ProductLineKey.Equal(productLineKey)));
         using (var adapter = new DataAccessAdapter())
         {
             uow.Commit(adapter);
         }
     }

     public IList&lt;ProductLineEntity&gt; FindByName(string productLineName, bool includeProducts)
     {
         using (var adapter = new DataAccessAdapter())
         {
             var metaData = new LinqMetaData(adapter);
             if (includeProducts)
             {
                 return metaData.ProductLine.Where(x =&gt; x.ProductLineName == productLineName)
                       .WithPath(p =&gt; p.Prefetch(pl =&gt; pl.Products)).ToList();
             }
             return metaData.ProductLine.Where(x =&gt; x.ProductLineName == productLineName).ToList();
         }
     }

     public IList&lt;ProductLineEntity&gt; GetAll(bool includeProducts)
     {
         using (var adapter = new DataAccessAdapter())
         {
             var metaData = new LinqMetaData(adapter);
             if (includeProducts)
             {
                 return metaData.ProductLine.WithPath(p =&gt; p.Prefetch(pl =&gt; pl.Products)).ToList();
             }
             return metaData.ProductLine.ToList();
         }
     }

     public ProductLineEntity? GetByKey(int productLineKey, bool includeProducts)
     {
         using (var adapter = new DataAccessAdapter())
         {
             var metaData = new LinqMetaData(adapter);
             if (includeProducts)
             {
                 var toReturn = metaData.ProductLine.Where(x =&gt; x.ProductLineKey == productLineKey)
                               .WithPath(p =&gt; p.Prefetch(pl =&gt; pl.Products)).SingleOrDefault();
                 if (toReturn != null)
                 {
                     // insert removal tracker for tracking removed entities.
                     toReturn.Products.RemovedEntitiesTracker = new EntityCollection&lt;ProductEntity&gt;();
                 }
                 return toReturn;
             }
             return metaData.ProductLine.SingleOrDefault(x =&gt; x.ProductLineKey == productLineKey);
         }
     }

     public void Update(ProductEntity product)
     {
         if (product == null)
             throw new ArgumentNullException(nameof(product), $&quot;{nameof(product)} is null.&quot;);

         using (var adapter = new DataAccessAdapter())
         {
             adapter.SaveEntity(product);
         }
     }

     public void Update(ProductLineEntity productLine)
     {
         if (productLine == null)
             throw new ArgumentNullException(nameof(productLine), $&quot;{nameof(productLine)} is null.&quot;);

// Specify the order of operations for the unit of work, so it will first perform delete operations
// directly on the database and then do inserts followed by updates. We have to specify the order
// here as it's different from the default, where DeletesPerformedDirectly are done last. 
var uow = new UnitOfWork2(new List&lt;UnitOfWorkBlockType&gt;()
                          {
                              UnitOfWorkBlockType.DeletesPerformedDirectly,
                              UnitOfWorkBlockType.Inserts, 
                              UnitOfWorkBlockType.Updates
                          });
         uow.AddForSave(productLine, null, refetch: true, recurse: false);
         using (var adapter = new DataAccessAdapter())
         {
             uow.Commit(adapter);
         }
     }

     public void UpdateGraph(ProductLineEntity productLine)
     {
         if (productLine == null)
             throw new ArgumentNullException(nameof(productLine), $&quot;{nameof(productLine)} is null.&quot;);

var uow = new UnitOfWork2(new List&lt;UnitOfWorkBlockType&gt;()
                          {
                              UnitOfWorkBlockType.DeletesPerformedDirectly,
                              UnitOfWorkBlockType.Inserts,
                              UnitOfWorkBlockType.Updates
                          });
         uow.AddForSave(productLine);
         using (var adapter = new DataAccessAdapter())
         {
             uow.Commit(adapter);
         }
     }

     public virtual void UpdateGraphWithChildDeletes(ProductLineEntity productLine)
     {
         if (productLine == null)
             throw new ArgumentNullException(nameof(productLine), $&quot;{nameof(productLine)} is null.&quot;);

         // this update method will update the related products. Any removed product has to be removed as it's orphaned.
         // We have to remove all products which key isn't in the set of products currently related to the passed in productline.
         // To do that we'll do a delete directly using a where clause where all entities with a key not in the set of
         // keys of the current related product entities are removed. We'll wrap it all in a unit of work for easy transaction handling.
         // In the unit of work, we have to schedule the direct deletes before the insert of the new row, otherwise it's removed,
         // as it doesn't have a PK yet, so the IN clause we're using won't match it.
         var currentKeys = productLine.Products.Select(p =&gt; p.ProductKey).ToList();
var uow = new UnitOfWork2(new List&lt;UnitOfWorkBlockType&gt;()
                          {
                              UnitOfWorkBlockType.DeletesPerformedDirectly,
                              UnitOfWorkBlockType.Inserts, 
                              UnitOfWorkBlockType.Updates
                          });
         uow.AddDeleteEntitiesDirectlyCall(typeof(ProductEntity), new RelationPredicateBucket(ProductFields.ProductKey.NotIn(currentKeys)));
         uow.AddForSave(productLine);
         using (var adapter = new DataAccessAdapter())
         {
             uow.Commit(adapter);
         }
     }

     public void UpdateGraphWithDeletes(ProductLineEntity productLine, IList&lt;int&gt; productKeysToRemove)
     {
         if (productLine == null)
             throw new ArgumentNullException(nameof(productLine), $&quot;{nameof(productLine)} is null.&quot;);

var uow = new UnitOfWork2(new List&lt;UnitOfWorkBlockType&gt;()
                          {
                              UnitOfWorkBlockType.DeletesPerformedDirectly,
                              UnitOfWorkBlockType.Inserts,
                              UnitOfWorkBlockType.Updates
                          });
         if (productKeysToRemove?.Count &gt; 0)
             uow.AddDeleteEntitiesDirectlyCall(typeof(ProductEntity), new RelationPredicateBucket(ProductFields.ProductKey.In(productKeysToRemove)));
         uow.AddForSave(productLine);
         using (var adapter = new DataAccessAdapter())
         {
             uow.Commit(adapter);
         }
     }
 }
</code></pre>

<p>Alternative Update method using removal tracker functionality</p>
<pre><code class="cs">public class ModelWithChildrenScenarioAlt : ModelWithChildrenScenario
{
    public override void UpdateGraphWithChildDeletes(ProductLineEntity productLine)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $&quot;{nameof(productLine)} is null.&quot;);

        // this update method will update the related products. Any removed product has to be removed as it's orphaned.
        // we inserted a removal tracker in the productline entity to track these, so we can just delete them from
        // this collection. We also have to update the entity and related entities. We'll use a unit of work object
        // for this to have easy transaction management.
        var uow = new UnitOfWork2();
        uow.AddForSave(productLine);
        uow.AddCollectionForDelete(productLine.Products.RemovedEntitiesTracker);
        using (var adapter = new DataAccessAdapter())
        {
            uow.Commit(adapter);
        }
    }
}
</code></pre>

<h2 id="nhibernate">NHibernate<a class="headerlink" href="#nhibernate" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<p>By default, NHibernate does not support a clean separation between the data access layer and the rest of the application. This is due to the way the lazy-loading works, which requires an active <code>ISession</code> even when lazy-loading isn't desired.</p>
<p>The work-around is to explicitly trigger lazy-loading when the child rows are desired. When the child rows are not desired, block lazy-loading by setting the collection property to an empty list.</p>
<p>For partial deletes, ensure that you are using <code>cascade=&quot;all-delete-orphan&quot;</code>. Otherwise it will ignore the missing child rows. (Alternately, you can pass in a separate list of rows to delete.)</p>
<pre><code class="cs">public class ModelWithChildrenScenario : IModelWithChildrenScenario&lt;ProductLine, Product&gt;
{
    readonly ISessionFactory m_SessionFactory;

    public ModelWithChildrenScenario(ISessionFactory sessionFactory)
    {
        m_SessionFactory = sessionFactory;
    }

    public int Create(ProductLine productLine)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $&quot;{nameof(productLine)} is null.&quot;);

        productLine.ApplyKeys();

        using (var session = m_SessionFactory.OpenSession())
        {
            session.Save(productLine);
            session.Flush();
            return productLine.ProductLineKey;
        }
    }

    public void Delete(ProductLine productLine)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $&quot;{nameof(productLine)} is null.&quot;);

        using (var session = m_SessionFactory.OpenSession())
        {
            session.Delete(productLine);
            session.Flush();
        }
    }

    public void DeleteByKey(int productLineKey)
    {
        using (var session = m_SessionFactory.OpenSession())
        {
            var temp = session.Get&lt;ProductLine&gt;(productLineKey);

            session.Delete(temp);
            session.Flush();
        }
    }

    public IList&lt;ProductLine&gt; FindByName(string productLineName, bool includeProducts)
    {
        using (var session = m_SessionFactory.OpenSession())
        {
            var result = session.QueryOver&lt;ProductLine&gt;().Where(pl =&gt; pl.ProductLineName == productLineName).List();

            if (includeProducts)
                result.SelectMany(x =&gt; x.Products).All(x =&gt; true); //force lazy-load
            else
                foreach (var line in result)
                    line.Products = new List&lt;Product&gt;();//disable lazy-loading

            return result;
        }
    }

    public IList&lt;ProductLine&gt; GetAll(bool includeProducts)
    {
        using (var session = m_SessionFactory.OpenSession())
        {
            var result = session.QueryOver&lt;ProductLine&gt;().List();

            if (includeProducts)
                result.SelectMany(x =&gt; x.Products).All(x =&gt; true); //force lazy-load
            else
                foreach (var line in result)
                    line.Products = new List&lt;Product&gt;();//disable lazy-loading

            return result;
        }
    }

    public ProductLine? GetByKey(int productLineKey, bool includeProducts)
    {
        using (var session = m_SessionFactory.OpenSession())
        {
            var result = session.Get&lt;ProductLine&gt;(productLineKey);
            if (result != null)
            {
                if (includeProducts)
                    result.Products.All(x =&gt; true); //force lazy-load
                else
                    result.Products = new List&lt;Product&gt;(); //disable lazy-loading
            }
            return result;
        }
    }

    public void UpdateGraphWithChildDeletes(ProductLine productLine)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $&quot;{nameof(productLine)} is null.&quot;);

        productLine.ApplyKeys();

        using (var session = m_SessionFactory.OpenSession())
        using (var trans = session.BeginTransaction())
        {
            session.Update(productLine);
            session.Flush();
            trans.Commit();
        }
    }

    public void Update(Product product)
    {
        if (product == null)
            throw new ArgumentNullException(nameof(product), $&quot;{nameof(product)} is null.&quot;);

        using (var session = m_SessionFactory.OpenSession())
        using (var trans = session.BeginTransaction())
        {
            session.Merge(product);
            session.Flush();
            trans.Commit();
        }
    }

    public void Update(ProductLine productLine)
    {
        throw new AssertInconclusiveException();
    }

    public void UpdateGraph(ProductLine productLine)
    {
        throw new AssertInconclusiveException();
    }

    public void UpdateGraphWithDeletes(ProductLine productLine, IList&lt;int&gt; productKeysToRemove)
    {
        throw new AssertInconclusiveException();
    }
}
</code></pre>

<h2 id="repodb">RepoDb<a class="headerlink" href="#repodb" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<pre><code class="cs">public class ModelWithChildrenScenario : DbRepository&lt;SqlConnection&gt;,
    IModelWithChildrenScenario&lt;ProductLine, Product&gt;
{
    public ModelWithChildrenScenario(string connectionString)
        : base(connectionString, RDB.Enumerations.ConnectionPersistency.Instance)
    { }

    public int Create(ProductLine productLine)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $&quot;{nameof(productLine)} is null.&quot;);

        var key = Insert&lt;ProductLine, int&gt;(productLine);
        productLine.ApplyKeys();
        InsertAll(productLine.Products);
        return key;
    }

    private void ExecuteDelete(int productLineKey)
    {
        var sql = @&quot;DELETE FROM Production.Product WHERE ProductLineKey = @ProductLineKey;
            DELETE FROM Production.ProductLine WHERE ProductLineKey = @ProductLineKey;&quot;;

        using (var con = CreateConnection(true))
        {
            con.ExecuteNonQuery(sql, new { productLineKey });
        }
    }

    public void Delete(ProductLine productLine)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $&quot;{nameof(productLine)} is null.&quot;);

        //base.Delete(productLine);
        ExecuteDelete(productLine.ProductLineKey);
    }

    public void DeleteByKey(int productLineKey)
    {
        //base.Delete&lt;ProductLine&gt;(productLineKey);
        ExecuteDelete(productLineKey);
    }

    private void FetchProducts(IEnumerable&lt;ProductLine&gt; productLines)
    {
        var keys = productLines.Select(e =&gt; e.ProductLineKey).AsList();
        Query&lt;Product&gt;(e =&gt; keys.Contains(e.ProductLineKey))
            .AsList()
            .ForEach(p =&gt;
                productLines.First(e =&gt; e.ProductLineKey == p.ProductLineKey).Products.Add(p));
    }

    public IList&lt;ProductLine&gt; FindByName(string productLineName, bool includeProducts)
    {
        var lines = Query&lt;ProductLine&gt;(e =&gt; e.ProductLineName == productLineName);
        if (includeProducts)
            FetchProducts(lines);
        return lines.AsList();
    }

    public IList&lt;ProductLine&gt; GetAll(bool includeProducts)
    {
        var lines = QueryAll&lt;ProductLine&gt;();
        if (includeProducts)
            FetchProducts(lines);
        return lines.AsList();
    }

    public ProductLine? GetByKey(int productLineKey, bool includeProducts)
    {
        var line = Query&lt;ProductLine&gt;(productLineKey).FirstOrDefault();
        if (includeProducts &amp;&amp; null != line)
            line.Products.AddRange(
                Query&lt;Product&gt;(e =&gt; e.ProductLineKey == line.ProductLineKey));
        return line;
    }

    public void Update(ProductLine productLine)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $&quot;{nameof(productLine)} is null.&quot;);

        base.Update(productLine);
    }

    public void Update(Product product)
    {
        if (product == null)
            throw new ArgumentNullException(nameof(product), $&quot;{nameof(product)} is null.&quot;);

        base.Update(product);
    }

    public void UpdateGraph(ProductLine productLine)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $&quot;{nameof(productLine)} is null.&quot;);

        productLine.ApplyKeys();

        Update(productLine);
        MergeAll(productLine.Products);
    }

    public void UpdateGraphWithChildDeletes(ProductLine productLine)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $&quot;{nameof(productLine)} is null.&quot;);

        productLine.ApplyKeys();

        var products = Query&lt;Product&gt;(p =&gt; p.ProductLineKey == productLine.ProductLineKey);
        var originalProductKeys = products
            .Select(p =&gt; p.ProductKey);
        var currentProductKeys = productLine
            .Products
            .Select(e =&gt; e.ProductKey);
        var productKeysToRemove = originalProductKeys
            .Except(currentProductKeys)
            .AsList();

        UpdateGraphWithDeletes(productLine, productKeysToRemove);
    }

    public void UpdateGraphWithDeletes(ProductLine productLine, IList&lt;int&gt; productKeysToRemove)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $&quot;{nameof(productLine)} is null.&quot;);

        productLine.ApplyKeys();

        Update(productLine);

        if (productKeysToRemove?.Any() == true)
            Delete&lt;Product&gt;(e =&gt; productKeysToRemove.Contains(e.ProductKey));

        if (productLine.Products?.Any() == true)
            MergeAll&lt;Product&gt;(productLine.Products);
    }
}
</code></pre>

<h2 id="servicestack">ServiceStack<a class="headerlink" href="#servicestack" title="Permalink to this headline"><i class="fa fa-link" aria-hidden="true"></i></a></h2>
<pre><code class="cs">public class ModelWithChildrenScenario : IModelWithChildrenScenario&lt;ProductLine, Product&gt;
{
    private readonly IDbConnectionFactory _dbConnectionFactory;
    public ModelWithChildrenScenario(IDbConnectionFactory dbConnectionFactory)
    {
        _dbConnectionFactory = dbConnectionFactory;
    }

    public int Create(ProductLine productLine)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $&quot;{nameof(productLine)} is null.&quot;);

        using (var db = _dbConnectionFactory.OpenDbConnection())
            db.Save(productLine, true);

        return productLine.Id;
    }

    public void Delete(ProductLine productLine)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $&quot;{nameof(productLine)} is null.&quot;);
        
        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            db.Delete&lt;Product&gt;(r =&gt; r.ProductLineId == productLine.Id);
            db.Delete(productLine);
        }
    }

    public void DeleteByKey(int productLineKey)
    {
        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            db.Delete&lt;Product&gt;(r =&gt; r.ProductLineId == productLineKey);
            db.DeleteById&lt;ProductLine&gt;(productLineKey);
        }
    }

    public IList&lt;ProductLine&gt; FindByName(string productLineName, bool includeProducts)
    {
        if (string.IsNullOrWhiteSpace(productLineName))
            throw new ArgumentNullException(nameof(productLineName), $&quot;{nameof(productLineName)} is null or empty.&quot;);
        
        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            return includeProducts
                ? db.LoadSelect&lt;ProductLine&gt;(e =&gt; e.ProductLineName == productLineName)
                : db.Select&lt;ProductLine&gt;(e =&gt; e.ProductLineName == productLineName);
        }
    }

    public IList&lt;ProductLine&gt; GetAll(bool includeProducts)
    {
        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            return includeProducts
                ? db.LoadSelect&lt;ProductLine&gt;()
                : db.Select&lt;ProductLine&gt;();
        }
    }

    public ProductLine? GetByKey(int productLineKey, bool includeProducts)
    {
        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            var products = includeProducts
                ? db.LoadSingleById&lt;ProductLine&gt;(productLineKey)
                : db.SingleById&lt;ProductLine&gt;(productLineKey);
            return products;
        }
    }

    public void Update(ProductLine productLine)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $&quot;{nameof(productLine)} is null.&quot;);
        
        using (var db = _dbConnectionFactory.OpenDbConnection())
            db.Update(productLine);
    }

    public void UpdateGraph(ProductLine productLine)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $&quot;{nameof(productLine)} is null.&quot;);
        
        using (var db = _dbConnectionFactory.OpenDbConnection())
            db.Save(productLine, true);
    }

    public void UpdateGraphWithChildDeletes(ProductLine productLine)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $&quot;{nameof(productLine)} is null.&quot;);
        
        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            db.Save(productLine, true);

            var productIdsToKeep = productLine.Products.Select(p =&gt; p.Id).ToList();

            db.Delete&lt;Product&gt;(p =&gt;
                p.ProductLineId == productLine.Id &amp;&amp; !Sql.In(p.Id, productIdsToKeep));
        }
    }

    public void UpdateGraphWithDeletes(ProductLine productLine, IList&lt;int&gt; productKeysToRemove)
    {
        if (productLine == null)
            throw new ArgumentNullException(nameof(productLine), $&quot;{nameof(productLine)} is null.&quot;);
        
        using (var db = _dbConnectionFactory.OpenDbConnection())
        {
            db.Save(productLine, true);

            db.Delete&lt;Product&gt;(p =&gt;
                p.ProductLineId == productLine.Id &amp;&amp; Sql.In(p.Id, productKeysToRemove));
        }
    }

    public void Update(Product product)
    {
        if (product == null)
            throw new ArgumentNullException(nameof(product), $&quot;{nameof(product)} is null.&quot;);
        
        using (var db = _dbConnectionFactory.OpenDbConnection())
            db.Update(product);
    }
}
</code></pre>


                    </div>
                </div>
                <footer>
                    <hr />
                    <div role="contentinfo">
The ORM Cookbook. <a href='https://github.com/Grauenwolf/DotNet-ORM-Cookbook' target='_blank'>Visit us at GitHub</a>.
                    </div>
                </footer>
            </div>
        </section>
    </div>
    <script src="js/jquery-2.1.1.min.js"></script>
    <script src="js/modernizr-2.8.3.min.js"></script>
    <script src="js/highlight.pack.js"></script>
    <script src="js/theme.js"></script>

</body>
</html>
